package com.supermarket.dao;

import com.supermarket.model.Promotion;
import com.supermarket.model.Product;
import com.supermarket.util.DatabaseUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 促销活动数据访问对象
 * 负责促销相关的数据库操作
 */
public class PromotionDAO {

    /**
     * 获取所有促销活动
     * 
     * @return 促销活动列表
     */
    public List<Promotion> getAllPromotions() {
        List<Promotion> promotions = new ArrayList<>();
        String sql = "SELECT p.id, p.promotion_code, p.promotion_name, p.promotion_type, " +
                "       p.discount_rate, p.discount_amount, p.min_quantity, p.start_date, p.end_date, " +
                "       p.status, p.created_by, p.created_at, p.updated_at, u.real_name AS created_by_name " +
                "FROM promotions p " +
                "LEFT JOIN users u ON p.created_by = u.id " +
                "ORDER BY p.created_at DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                Promotion promotion = createPromotionFromResultSet(rs);
                promotions.add(promotion);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return promotions;
    }

    /**
     * 根据状态筛选促销活动
     * 
     * @param status 促销状态
     * @return 促销活动列表
     */
    public List<Promotion> getPromotionsByStatus(String status) {
        List<Promotion> promotions = new ArrayList<>();
        String sql = "SELECT p.id, p.promotion_code, p.promotion_name, p.promotion_type, " +
                "       p.discount_rate, p.discount_amount, p.min_quantity, p.start_date, p.end_date, " +
                "       p.status, p.created_by, p.created_at, p.updated_at, u.real_name AS created_by_name " +
                "FROM promotions p " +
                "LEFT JOIN users u ON p.created_by = u.id " +
                "WHERE p.status = ? " +
                "ORDER BY p.created_at DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, status);
            rs = ps.executeQuery();

            while (rs.next()) {
                Promotion promotion = createPromotionFromResultSet(rs);
                promotions.add(promotion);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return promotions;
    }

    /**
     * 根据促销类型筛选促销活动
     * 
     * @param type 促销类型
     * @return 促销活动列表
     */
    public List<Promotion> getPromotionsByType(String type) {
        List<Promotion> promotions = new ArrayList<>();
        String sql = "SELECT p.id, p.promotion_code, p.promotion_name, p.promotion_type, " +
                "       p.discount_rate, p.discount_amount, p.min_quantity, p.start_date, p.end_date, " +
                "       p.status, p.created_by, p.created_at, p.updated_at, u.real_name AS created_by_name " +
                "FROM promotions p " +
                "LEFT JOIN users u ON p.created_by = u.id " +
                "WHERE p.promotion_type = ? " +
                "ORDER BY p.created_at DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, type);
            rs = ps.executeQuery();

            while (rs.next()) {
                Promotion promotion = createPromotionFromResultSet(rs);
                promotions.add(promotion);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return promotions;
    }

    /**
     * 根据ID获取促销活动详情
     * 
     * @param promotionId 促销活动ID
     * @return 促销活动对象
     */
    public Promotion getPromotionById(int promotionId) {
        String sql = "SELECT p.id, p.promotion_code, p.promotion_name, p.promotion_type, " +
                "       p.discount_rate, p.discount_amount, p.min_quantity, p.start_date, p.end_date, " +
                "       p.status, p.created_by, p.created_at, p.updated_at, u.real_name AS created_by_name " +
                "FROM promotions p " +
                "LEFT JOIN users u ON p.created_by = u.id " +
                "WHERE p.id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, promotionId);
            rs = ps.executeQuery();

            if (rs.next()) {
                return createPromotionFromResultSet(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return null;
    }

    /**
     * 添加新的促销活动
     * 
     * @param promotion 促销活动对象
     * @return 是否添加成功
     */
    public boolean addPromotion(Promotion promotion) {
        // 先检查促销编码是否已存在
        if (isPromotionCodeExists(promotion.getPromotionCode())) {
            throw new RuntimeException("促销编码 " + promotion.getPromotionCode() + " 已存在，请使用其他编码");
        }

        String sql = "INSERT INTO promotions (promotion_code, promotion_name, promotion_type, " +
                "discount_rate, discount_amount, min_quantity, start_date, end_date, " +
                "status, created_by) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, promotion.getPromotionCode());
            ps.setString(2, promotion.getPromotionName());
            ps.setString(3, promotion.getPromotionType());
            ps.setBigDecimal(4, promotion.getDiscountRate());
            ps.setBigDecimal(5, promotion.getDiscountAmount());
            ps.setInt(6, promotion.getMinQuantity());
            ps.setDate(7, promotion.getStartDate());
            ps.setDate(8, promotion.getEndDate());
            ps.setString(9, promotion.getStatus());
            ps.setInt(10, promotion.getCreatedBy());

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 更新促销活动信息
     * 
     * @param promotion 促销活动对象
     * @return 是否更新成功
     */
    public boolean updatePromotion(Promotion promotion) {
        String sql = "UPDATE promotions " +
                "SET promotion_name = ?, promotion_type = ?, discount_rate = ?, " +
                "    discount_amount = ?, min_quantity = ?, start_date = ?, end_date = ?, " +
                "    status = ?, updated_at = CURRENT_TIMESTAMP " +
                "WHERE id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, promotion.getPromotionName());
            ps.setString(2, promotion.getPromotionType());
            ps.setBigDecimal(3, promotion.getDiscountRate());
            ps.setBigDecimal(4, promotion.getDiscountAmount());
            ps.setInt(5, promotion.getMinQuantity());
            ps.setDate(6, promotion.getStartDate());
            ps.setDate(7, promotion.getEndDate());
            ps.setString(8, promotion.getStatus());
            ps.setInt(9, promotion.getId());

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 更新促销活动状态
     * 
     * @param promotionId 促销活动ID
     * @param status      新状态
     * @return 是否更新成功
     */
    public boolean updatePromotionStatus(int promotionId, String status) {
        String sql = "UPDATE promotions SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, status);
            ps.setInt(2, promotionId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 删除促销活动（软删除，设置状态为CANCELLED）
     * 
     * @param promotionId 促销活动ID
     * @return 是否删除成功
     */
    public boolean deletePromotion(int promotionId) {
        return updatePromotionStatus(promotionId, "CANCELLED");
    }

    /**
     * 检查促销编码是否已存在
     * 
     * @param promotionCode 促销编码
     * @return 是否存在
     */
    public boolean isPromotionCodeExists(String promotionCode) {
        String sql = "SELECT COUNT(*) FROM promotions WHERE promotion_code = ? AND status != 'CANCELLED'";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, promotionCode);
            rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return false;
    }

    /**
     * 获取促销活动关联的商品列表
     * 
     * @param promotionId 促销活动ID
     * @return 商品列表
     */
    public List<Product> getPromotionProducts(int promotionId) {
        List<Product> products = new ArrayList<>();
        String sql = "SELECT p.id, p.product_code, p.product_name, c.category_name, s.supplier_name, " +
                "       p.unit, p.purchase_price, p.sale_price, p.description, p.status, " +
                "       i.current_stock " +
                "FROM products p " +
                "LEFT JOIN categories c ON p.category_id = c.id " +
                "LEFT JOIN suppliers s ON p.supplier_id = s.id " +
                "LEFT JOIN inventory i ON p.id = i.product_id " +
                "INNER JOIN product_promotions pp ON p.id = pp.product_id " +
                "WHERE pp.promotion_id = ? AND p.status = 'ACTIVE' " +
                "ORDER BY p.product_name";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, promotionId);
            rs = ps.executeQuery();

            while (rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setProductCode(rs.getString("product_code"));
                product.setProductName(rs.getString("product_name"));
                product.setCategoryName(rs.getString("category_name"));
                product.setSupplierName(rs.getString("supplier_name"));
                product.setUnit(rs.getString("unit"));
                product.setPurchasePrice(rs.getBigDecimal("purchase_price"));
                product.setSalePrice(rs.getBigDecimal("sale_price"));
                product.setDescription(rs.getString("description"));
                product.setStatus(rs.getString("status"));
                product.setCurrentStock(rs.getBigDecimal("current_stock"));
                products.add(product);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return products;
    }

    /**
     * 为促销活动添加商品
     * 
     * @param promotionId 促销活动ID
     * @param productId   商品ID
     * @return 是否添加成功
     */
    public boolean addPromotionProduct(int promotionId, int productId) {
        String sql = "INSERT INTO product_promotions (product_id, promotion_id) VALUES (?, ?)";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, productId);
            ps.setInt(2, promotionId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 从促销活动中移除商品
     * 
     * @param promotionId 促销活动ID
     * @param productId   商品ID
     * @return 是否移除成功
     */
    public boolean removePromotionProduct(int promotionId, int productId) {
        String sql = "DELETE FROM product_promotions WHERE product_id = ? AND promotion_id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, productId);
            ps.setInt(2, promotionId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 获取商品的有效促销活动
     * 
     * @param productId 商品ID
     * @return 促销活动列表
     */
    public List<Promotion> getActivePromotionsForProduct(int productId) {
        List<Promotion> promotions = new ArrayList<>();
        String sql = "SELECT p.id, p.promotion_code, p.promotion_name, p.promotion_type, " +
                "       p.discount_rate, p.discount_amount, p.min_quantity, p.start_date, p.end_date, " +
                "       p.status, p.created_by, p.created_at, p.updated_at, u.real_name AS created_by_name " +
                "FROM promotions p " +
                "LEFT JOIN users u ON p.created_by = u.id " +
                "INNER JOIN product_promotions pp ON p.id = pp.promotion_id " +
                "WHERE pp.product_id = ? " +
                "AND p.status = 'ACTIVE' " +
                "AND CURDATE() BETWEEN p.start_date AND p.end_date " +
                "ORDER BY p.discount_rate DESC, p.discount_amount DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, productId);
            rs = ps.executeQuery();

            while (rs.next()) {
                Promotion promotion = createPromotionFromResultSet(rs);
                promotions.add(promotion);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return promotions;
    }

    /**
     * 自动更新过期促销活动状态
     * 
     * @return 更新的记录数
     */
    public int updateExpiredPromotions() {
        String sql = "UPDATE promotions SET status = 'EXPIRED', updated_at = CURRENT_TIMESTAMP " +
                "WHERE status = 'ACTIVE' AND end_date < CURDATE()";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 从ResultSet创建Promotion对象（重用代码）
     */
    private Promotion createPromotionFromResultSet(ResultSet rs) throws SQLException {
        Promotion promotion = new Promotion();
        promotion.setId(rs.getInt("id"));
        promotion.setPromotionCode(rs.getString("promotion_code"));
        promotion.setPromotionName(rs.getString("promotion_name"));
        promotion.setPromotionType(rs.getString("promotion_type"));
        promotion.setDiscountRate(rs.getBigDecimal("discount_rate"));
        promotion.setDiscountAmount(rs.getBigDecimal("discount_amount"));
        promotion.setMinQuantity(rs.getInt("min_quantity"));
        promotion.setStartDate(rs.getDate("start_date"));
        promotion.setEndDate(rs.getDate("end_date"));
        promotion.setStatus(rs.getString("status"));
        promotion.setCreatedBy(rs.getInt("created_by"));
        promotion.setCreatedAt(rs.getTimestamp("created_at"));
        promotion.setUpdatedAt(rs.getTimestamp("updated_at"));
        promotion.setCreatedByName(rs.getString("created_by_name"));
        return promotion;
    }
}
